package Besuperdb;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.codec.digest.DigestUtils;

import BesuperApp.pojo.Answer;
import BesuperApp.pojo.Player;
import BesuperApp.pojo.Question;

public class BesuperDao {
	private final String driver;
	private final String url;
	private final String u;
	private final String p;

	private Connection conn = null;
	private String sql;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

	public BesuperDao() {
		init();
	}

	{
		Properties props = new Properties();
		try {
			props.load(new FileInputStream("jdbc.properties"));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		driver = props.getProperty("db.driver");
		url = props.getProperty("db.url");
		u = props.getProperty("db.username");
		p = props.getProperty("db.password");
	}

	public void init() {
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, u, p);
			System.out.println("Connect to database successfully!");
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}

	public Player login(String username, String password) {
		Player player = null;
		try {
			sql = "select * from player where username=? and userpassword =?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, DigestUtils.md5Hex(password));
			rs = ps.executeQuery();
			if (rs != null) {
				while (rs.next()) {
					int id = rs.getInt("id");
					String name = rs.getString("name");
					int score = rs.getInt("score");
					player = new Player(id, name, score);
				}
				return player;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			clearup(null, ps, rs);
		}
		return player;
	}

	public Player register(String name, String username, String password) {

		try {
			sql = "insert into player(name,username,userpassword) values(?,?,?)";

			ps = conn.prepareStatement(sql);

			ps.setString(1, name);
			ps.setString(2, username);
			ps.setString(3, DigestUtils.md5Hex(password));
			int isTrue = ps.executeUpdate();
			if (isTrue > 0) {
				return login(username, password);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(null, ps, null);
		}
		return null;
	}

	public List<Player> listPlayer() {
		List<Player> list = new ArrayList<Player>();
		try {
			sql = "select id, name ,score from Player order by desc";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs != null) {
				while (rs.next()) {
					Player player = new Player();
					player.setId(rs.getInt("id"));
					player.setName(rs.getString("name"));
					player.setScore(rs.getInt("score"));
					list.add(player);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			clearup(conn, ps, rs);
		}
		return list;
	}

	public List<Question> listQuestions() {
		List<Question> list = new ArrayList<Question>();
		try {
			sql = "select * from question  ";
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs != null) {
				while(rs.next()){
					Question q = new Question();
					q.setId(rs.getInt("id"));
					q.setValue(rs.getString("value"));
					q.setFrom(this.getPlayer(rs.getInt("player_id")));
					q.setCredit(rs.getInt("credit"));
					q.setOpen("Y".equals(rs.getString("is_open")));
					list.add(q);
				}
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(conn, ps, rs);
		}
		return list;
	}

	public List<Question> listQuestion(Player player) {

		List<Question> list = new ArrayList<Question>();
		sql = "select id,value,credit,is_open from question where player_id =?";

		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, player.getId());
			rs = ps.executeQuery();
			if (rs != null) {
				while(rs.next()){
					
					Question q = new Question();
					q.setId(rs.getInt("id"));
					q.setValue(rs.getString("value"));
					q.setFrom(player);
					q.setCredit(rs.getInt("credit"));
					q.setOpen("Y".equals(rs.getString("is_open")));
					list.add(q);
				}
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(conn, ps, rs);
		}

		return list;

	}

	public Question getQuestion(long questionId) {
		Question q = null;
		sql = "select id ,value,credit,player_id,is_open from question where id =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, questionId);
			rs = ps.executeQuery();
			if (rs != null) {
				while(rs.next()){
					long id = rs.getInt("id");
					String value = rs.getString("value");
					long playerId = rs.getInt("player_id");
					int credit = rs.getInt("credit");
					boolean open = "Y".equals(rs.getString("is_open"));
					
					Player player = this.getPlayer(playerId);
					q = new Question(id, value, player, credit,open);
	
					List<Answer> answer = this.getAnswersFrom(q);
					q.setAnswers(answer);
					break;
				}
			}
			return q;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(conn, ps, rs);
		}
		return q;
	}

	public List<Answer> getAnswersFrom(Question q) {
		List<Answer> answer = new ArrayList<>();
		sql = "select * from answer where question_id =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, q.getId());
			rs = ps.executeQuery();
			if (rs != null) {
				while(rs.next()){
					Answer an = new Answer();
					an.setId(rs.getInt("id"));
					an.setValue(rs.getString("value"));
					an.setFrom(this.getPlayer(rs.getInt("player_id")));
					System.out.println(rs.getString("best"));
	//				an.setBestAnswer("Y".equals(rs.getString("best")));
					an.setQuestionId(rs.getInt("question_id"));
					answer.add(an);
				}
			}
			return answer;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(conn, ps, rs);
		}
		return answer;
	}

	public List<Answer> getAnswersFrom(Player player) {
		List<Answer> answer = new ArrayList<>();
		sql = "select id ,value ,question_id,best from answer where player_id=?";

		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, player.getId());
			rs = ps.executeQuery();
			if (rs != null) {
				while(rs.next()){
				Answer an = new Answer();
				an.setId(rs.getInt("id"));
				an.setValue(rs.getString("value"));
				an.setFrom(this.getPlayer(rs.getInt("player_id")));
				an.setQuestionId(rs.getInt("question_id"));
				an.setBestAnswer("Y".equals(rs.getString("best")));
				answer.add(an);
				}
			}
			return answer;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(conn, ps, rs);
		}
		return answer;

	}

	public Player getPlayer(long playerId) {
		Player player = null;
		sql = "select id ,name , score from player where id = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, playerId);
			rs = ps.executeQuery();
			if (rs != null) {
				while(rs.next()){
				long id = rs.getInt("id");
				String name = rs.getString("name");
				int score = rs.getInt("score");
				player = new Player(id, name, score);
				}
			}
			return player;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return player;
	}

	public List<Answer> listAnswer() {
		List<Answer> list = new ArrayList<>();
		sql = "select * from answer order by id";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			if (rs != null) {
				while(rs.next()){
				Answer an = new Answer();
				an.setId(rs.getInt("id"));
				an.setValue(rs.getString("value"));
				an.setFrom(this.getPlayer(rs.getInt("player_id")));
				an.setQuestionId(rs.getInt("question_id"));
				an.setBestAnswer("Y".equals(rs.getString("best")));
				list.add(an);
				}
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			clearup(conn, ps, rs);
		}
		return list;

	}

	public Answer getAnswers(long answerId) {

		Answer answer = null;
		sql = "select id ,value,player_id,question_id,best from answer where id =?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setLong(1, answerId);
			ResultSet rs = ps.executeQuery();
			if (rs != null) {
				while(rs.next()){
				answer = new Answer();
				answer.setId(rs.getInt("id"));
				answer.setValue(rs.getString("value"));
				answer.setFrom(this.getPlayer(rs.getInt("player_id")));
				answer.setQuestionId(rs.getInt("question_id"));
				answer.setBestAnswer("Y".equals(rs.getString("best")));
				}
			}
			return answer;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return answer;
	}

	public void upsertQuestion(Question question) {
		String sqlInsert="insert into question(value,credit,player_id,is_open)values(?,?,?,?)";
		String sqlUpdate="update question set value = ?,credit =?,player_id =?, is_open =? where id =?";
		
		boolean isUpdate = (question.getId() != -1);
		
		try {
			sql =sqlInsert;
			if(isUpdate){
				sql=sqlUpdate;
			}
			
			ps=conn.prepareStatement(sql);
			ps.setString(1,question.getValue());
			ps.setInt(2,question.getCredit() );
			ps.setLong(3, question.getFrom().getId());
			ps.setString(4, question.isOpen()? "Y":"N");
			if(isUpdate){
				ps.setLong(5, question.getId());
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			clearup(conn,ps,null);
		}
		
		
		

	}

	public void upsertAnswer(Answer answer) {
		
		String sqlInsert = "insert into answer(value,player_id,question_id,best) values(?,?,?,?)";
		String sqlUpdate ="update answer set value=?,player_id=?,question=?,best=? where id =?";
		boolean isUpdate = (answer.getId() != -1);
		
		try {
			    sql=sqlInsert;
			if(isUpdate){
				sql=sqlUpdate;
			}
			ps=conn.prepareStatement(sql);
			
			ps.setString(1, answer.getValue());
			ps.setLong(2, answer.getFrom().getId());
			ps.setLong(3, answer.getQuestionId());
			ps.setString(4, answer.isBestAnswer()?"Y":"N");
			if(isUpdate){
				ps.setLong(5, answer.getId());
			}
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			clearup(conn,ps,null);
		}
		

	}

	public void acceptAnswer(Question question, Answer answer) {
		PreparedStatement ps1 = null;
		sql = "update answer set best ='Y' where id=?";
		String sql1 = "update question set is_open ='N' where id =?";

		try {
			conn.setAutoCommit(false);

			ps = conn.prepareStatement(sql);
			ps.setLong(1, answer.getId());
			ps.execute();

			ps1 = conn.prepareStatement(sql1);
			ps1.setLong(1, question.getId());
			ps1.execute();

			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			clearup(conn, ps, rs);
		}

	}

	public boolean isUsernameExists(String username) {
		sql = "select 'Y' from player where username= ?";

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			clearup(null, ps, rs);
		}

		return false;
	}

	public static void clearup(Connection connection, Statement sta, ResultSet rs) {
//		try {
//			if (connection != null) {
//				connection.close();
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		try {
//			if (sta != null) {
//				sta.close();
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//		try {
//			if (rs != null) {
//				rs.close();
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
	}

}
